package com.ssms.service;

import cn.hutool.core.util.IdcardUtil;
import com.ssms.commons.Result;
import com.ssms.commons.StuQueryParam;
import com.ssms.commons.TablePage;
import com.ssms.dao.*;
import com.ssms.model.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class SysUserService {

    @Resource
    private ClazzMapper clazzMapper;
    @Resource
    private AcademyMapper academyMapper;
    @Resource
    private SysUserMapper sysUserMapper;
    @Resource
    private SubjectMapper subjectMapper;
    @Resource
    private StuDetailMapper stuDetailMapper;
    @Resource
    private ExamScoreMapper examScoreMapper;

    public Result save(SysUser sysUser) {
        Result check = check(sysUser);
        if (check != null) {
            return check;
        }

        int count = sysUserMapper.insertSelective(sysUser);
        return count > 0?Result.success():Result.failed("添加失败", 500);
    }


    public Result importStu(MultipartFile file) throws IOException {
        LinkedList<Map<String, String>> data = readData(file);
        List<Integer> errors = new ArrayList<>();
        List<Integer> repeateds = new ArrayList<>();
        Map<String, List> convert = convert(data, errors, repeateds);

        int i = 0;
        if(convert.size() > 0){
            List<SysUser> stus = convert.get("stus");
            List<StuDetail> stuDetails = convert.get("stuDetails");

            for (int j = 0; j < stus.size(); j++) {
                SysUser sysUser = stus.get(j);
                int x = sysUserMapper.insertSelective(sysUser);
                if(x > 0){
                    StuDetail stuDetail = stuDetails.get(j);
                    stuDetail.setUserId(sysUser.getId());
                    int y = 0;
                    try{
                        y = stuDetailMapper.insertSelective(stuDetail);
                    }catch (Exception e){
                        System.out.println("明细插入报错");
                    } finally {
                        if(y == 0){
                            sysUserMapper.deleteByPrimaryKey(sysUser.getId()); // 如果详情插入失败，前面插入的基本信息则删除
                        }else{
                            i++;
                        }
                    }
                }
            }
        }


        String msg = "<p>总计：【"+data.size()+"】行；</p><p>录入：【" + i + "】行；</p>";
        if(errors.size() > 0){
            msg += "<p>数据错误行号：【"+ StringUtils.join(errors,",") +"】；</p>";
        }
        if(repeateds.size() > 0){
            msg += "<p>数据重复行号：【"+ StringUtils.join(repeateds,",") +"】；</p>";
        }

        return Result.success(msg);
    }

    private Map<String,List> convert(LinkedList<Map<String,String>> list, List<Integer> errors, List<Integer> repeateds){
        Map map = new HashMap();
        List<SysUser> stus = new ArrayList<>();
        List<StuDetail> stuDetails = new ArrayList<>();


        for (int i = 0; i < list.size(); i++) {
            Map<String, String> item = list.get(i);

            Academy academy = academyMapper.selectByNo(item.get("academyNo"));
            if(academy == null){
                errors.add(i+2); // 院系编号错误 ，加2便于对应excel文件中行号
                continue;
            }

            Subject subject = subjectMapper.selectByNo(item.get("subjectNo"));
            if(subject == null || subject.getAcademyId() != academy.getId()){
                errors.add(i+2); // 专业编号错误
                continue;
            }

            Clazz clazz = clazzMapper.selectByClzNoAndSbjNo(item.get("clazzNo"), item.get("subjectNo"));
            if(clazz == null){
                errors.add(i+2); // 班级编号错误
                continue;
            }

            String cardNo = item.get("cardNo");
//            boolean validCard = IdcardUtil.isValidCard(cardNo); // 真实校验
            boolean validCard = regex18Check(cardNo); // 简单校验
            if(!validCard){
                errors.add(i+2);
                continue;
            }

            String phone = item.get("phone");
            if(!StringUtils.isEmpty(phone)){ // 选填
                if(!isPhone(phone)){
                    errors.add(i+2);
                    continue;
                }
            }

            String email = item.get("email");
            if(!StringUtils.isEmpty(email)){ // 选填
                boolean validEmail = isValidEmail(email);
                if(!validEmail){
                    errors.add(i+2);
                    continue;
                }
            }


            String code = item.get("stuNo");

            SysUser u = new SysUser();
            u.setPhone(phone);
            u.setCode(code);
            u.setClazzId(clazz.getId());
            u.setPassword(cardNo.substring(12,18)); // 默认密码身份证后6位
            u.setRoleType(3);
            u.setSex(item.get("sex"));
            u.setUsername(item.get("username"));

            StuDetail stuDetail = new StuDetail();
            stuDetail.setAcademyId(academy.getId());
            stuDetail.setZzmm(item.get("zzmm"));
            stuDetail.setAddress(item.get("address"));
            String birthday = cardNo.substring(6,15);
            stuDetail.setBirthday(birthday);
            stuDetail.setCardNo(cardNo);
            stuDetail.setEmail(email);
            stuDetail.setGrade((Integer.valueOf(item.get("grade"))));
            stuDetail.setJg(item.get("jg"));
            stuDetail.setMz(item.get("mz"));
            stuDetail.setSubjectId(subject.getId());
            stuDetail.setXj(item.get("xj"));
            stuDetail.setXz(subject.getXz());
            stuDetail.setYb(item.get("yb"));
            stuDetail.setEmail(email);


            if(sysUserMapper.checkPhone(u) > 0 || sysUserMapper.checkCode(u) > 0){
                repeateds.add(i+2);
                continue;
            }
            if(stuDetailMapper.checkCardNo(stuDetail) > 0 || stuDetailMapper.checkEmail(stuDetail) > 0){
                repeateds.add(i+2);
                continue;
            }

            stus.add(u);
            stuDetails.add(stuDetail);
        }

        map.put("stus",stus);
        map.put("stuDetails",stuDetails);
        return map;
    }

    // 简单校验身份证
    private static boolean regex18Check(String cardNo) {
        Pattern pattern = Pattern.compile("^(\\d{6}(18|19|20)\\d{2}(0[1-9]|1[12])(0[1-9]|[12]\\d|3[01])\\d{3}(\\d|X|x))$");
        Matcher m = pattern.matcher(cardNo);
        return (m.matches())?true:false;
    }

    public static boolean isValidEmail(String email) {
        if ((email != null) && (!email.isEmpty())) {
            return Pattern.matches("^(\\w+([-.][A-Za-z0-9]+)*){3,18}@\\w+([-.][A-Za-z0-9]+)*\\.\\w+([-.][A-Za-z0-9]+)*$", email);
        }
        return false;
    }

    private boolean isPhone(String str){
        final Pattern CHINA_PATTERN = Pattern.compile("^((13[0-9])|(14[0,1,4-9])|(15[0-3,5-9])|(16[2,5,6,7])|(17[0-8])|(18[0-9])|(19[0-3,5-9]))\\d{8}$");
        Matcher m = CHINA_PATTERN.matcher(str);
        return m.matches();
    }

    public LinkedList<Map<String,String>> readData(MultipartFile file) throws IOException {
        LinkedList<Map<String,String>> list = new LinkedList<>();

        Workbook wb0 = new XSSFWorkbook(file.getInputStream());
        //获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            if(r.getRowNum() < 1){
                continue;
            }

            // 逐个取出一行的每个单元格数据
            Map<String,String> item = new HashMap<>();
            item.put("stuNo",r.getCell(0).getStringCellValue());
            item.put("username",r.getCell(1).getStringCellValue());
            item.put("sex",r.getCell(2).getStringCellValue());
            item.put("grade",r.getCell(3).getStringCellValue());
            item.put("academyNo",r.getCell(4).getStringCellValue());
//            item.put("xz",r.getCell(5).getStringCellValue());
            item.put("zzmm",r.getCell(5).getStringCellValue());
            item.put("subjectNo",r.getCell(6).getStringCellValue());
            item.put("mz",r.getCell(7).getStringCellValue());
            item.put("clazzNo",r.getCell(8).getStringCellValue());
            item.put("xj",r.getCell(9).getStringCellValue());
            item.put("jg",r.getCell(10).getStringCellValue());
            item.put("cardNo",r.getCell(11).getStringCellValue());
            item.put("phone",r.getCell(12).getStringCellValue());
            item.put("email",r.getCell(13).getStringCellValue());
            item.put("yb",r.getCell(14).getStringCellValue());
            item.put("address",r.getCell(15).getStringCellValue());
//            item.put("address",r.getCell(16).getStringCellValue());
//            item.put("address",r.getCell(17).getStringCellValue());
            list.add(item);
        }
        return list;
    }

    // 辅导员管理分页查询
    public TablePage search(TablePage tablePage,String username,String sex) {
        int size = tablePage.getLimit();
        int start = (tablePage.getPage() - 1) * size;
        // 获取分页数据
        List<SysUser> list = sysUserMapper.selectPage(start, size, username,sex);
        // 获取符合条件的总数
        int count = sysUserMapper.selectPageCount(username,sex);
        tablePage.setCount(count);
        tablePage.setData(list);
        return tablePage;
    }

    // 学生管理分页查询
    public TablePage searchStu(TablePage tablePage, StuQueryParam queryParam) {
        int size = tablePage.getLimit();
        int start = (tablePage.getPage() - 1) * size;
        // 获取分页数据
        List<SysUser> list = sysUserMapper.selectStuPage(start, size, queryParam);
        // 获取符合条件的总数
        int count = sysUserMapper.selectStuPageCount(queryParam);
        tablePage.setCount(count);
        tablePage.setData(list);
        return tablePage;
    }

    // 毕业生管理分页查询
    public TablePage searchGraduation(TablePage tablePage, Integer grade) {
        int size = tablePage.getLimit();
        int start = (tablePage.getPage() - 1) * size;
        // 获取分页数据
        List<SysUser> list = sysUserMapper.selectGraduationPage(start, size, grade);
        // 获取符合条件的总数
        int count = sysUserMapper.selectGraduationPageCount(grade);
        tablePage.setCount(count);
        tablePage.setData(list);
        return tablePage;
    }

    // 更新辅导员
    public Result update(SysUser sysUser) {
        Result check = check(sysUser);
        if (check != null) {
            return check;
        }

        int count = sysUserMapper.updateByPrimaryKeySelective(sysUser);
        return count > 0?Result.success():Result.failed("更新失败", 500);
    }

    private Result check(SysUser sysUser){
        if(sysUserMapper.checkCode(sysUser) > 0){
            return Result.failed("编号重复",500);
        }

        if(sysUserMapper.checkCode(sysUser) > 0){
            return Result.failed("手机号重复",500);
        }
        return null;
    }

    public Result delete(Integer id) {

        List<Clazz> clazzes = clazzMapper.selectByInstructor(id);
        if(clazzes != null && clazzes.size() > 0){
            return Result.failed("该辅导员有带班，不能删除", 500);
        }
        int count = sysUserMapper.deleteByPrimaryKey(id);
        return count > 0?Result.success():Result.failed("删除失败", 500);
    }

    public Result all(Integer roleType) {
        return Result.success(sysUserMapper.selectAll(roleType));
    }

    public Result getInfo(Integer stuId) {
        return Result.success(sysUserMapper.selectStuById(stuId));
    }

    // 更新学生信息
    public Result updateStu(SysUser sysUser, StuDetail stuDetail) {
        if(sysUserMapper.checkCode(sysUser) > 0){
            return Result.failed("学号重复",500);
        }
        if(sysUserMapper.checkPhone(sysUser) > 0){
            return Result.failed("手机号重复",500);
        }

        sysUserMapper.updateByPrimaryKeySelective(sysUser);

        stuDetail.setUserId(sysUser.getId());
        boolean validCard = IdcardUtil.isValidCard(stuDetail.getCardNo());
        if(!validCard){
            return Result.failed("身份证号格式错误",500);
        }

        if(stuDetailMapper.checkCardNo(stuDetail) > 0){
            return Result.failed("身份证号重复",500);
        }

        if(stuDetailMapper.checkEmail(stuDetail) > 0){
            return Result.failed("邮箱重复",500);
        }
        stuDetailMapper.updateByPrimaryKeySelective(stuDetail);
        return Result.success();
    }

    public Result updateStu(StuDetail stuDetail) {
        stuDetailMapper.updateByPrimaryKeySelective(stuDetail);
        return Result.success();
    }

    // 手动添加学生
    public Result addStu(SysUser sysUser, StuDetail stuDetail) {
        if(sysUserMapper.checkCode(sysUser) > 0){
            return Result.failed("学号重复",500);
        }
        if(sysUserMapper.checkPhone(sysUser) > 0){
            return Result.failed("手机号重复",500);
        }

        sysUser.setPassword(sysUser.getPhone());
        sysUser.setRoleType(3);
        sysUserMapper.insertSelective(sysUser);
        stuDetail.setUserId(sysUser.getId());
        if(stuDetailMapper.checkCardNo(stuDetail) > 0){
            return Result.failed("身份证号重复",500);
        }

        if(stuDetailMapper.checkEmail(stuDetail) > 0){
            return Result.failed("邮箱重复",500);
        }
        stuDetailMapper.insertSelective(stuDetail);
        return Result.success();
    }

    public Result deleteStu(Integer id) {
        sysUserMapper.deleteByPrimaryKey(id);
        stuDetailMapper.deleteByUserId(id);
        examScoreMapper.deleteByStuId(id);
        return Result.success();
    }

    // 更新毕业生学籍
    public Result updateXjs(List<Integer> ids) {
        for (Integer id : ids) {
            StuDetail stuDetail = new StuDetail();
            stuDetail.setUserId(id);
            stuDetail.setXj("毕业");
            stuDetailMapper.updateByPrimaryKeySelective(stuDetail);
        }
        return Result.success();
    }

}
